<?php
  class SQLQuery{

    private $dbhost;
    private $dbuser;
    private $dbpass;
    private $conn;

    function __construct($dbhost=null, $dbuser=null, $dbpass=null){
      $this->dbhost = $dbhost;
      $this->dbuser = $dbuser;
      $this->dbpass = $dbpass;
      $this->conn   = @mysql_connect($this->dbhost, $this->dbuser, $this->dbpass);
    }

    public function readConnectDB(){
      $this->conn =  mysql_connect($this->dbhost, $this->dbuser, $this->dbpass);
      if (!$this->conn){
        die('Could not connect: ' . mysql_error()); exit;
      }
    }

    public function deleteConnectDB(){
      mysql_close($this->conn);
      unset($this->conn);
    }

    public function readAllDatabase(){
      $query = "SELECT DISTINCT(schemata.schema_name) as dbname
                FROM information_schema.columns, information_schema.schemata
                WHERE columns.table_schema = schemata.schema_name
              ";

      $stmt = mysql_query($query, $this->conn);
      $i=0;
      while($row = mysql_fetch_object($stmt)){
        $objResult->dbname[$i]=$row->dbname;
        $i++;
      }

      //print_r($objResult);
      //$rowsAffected = mysql_num_rows($stmt);
      //echo $rowsAffected;
      //mysql_close($con);
      //unset($con);

      return $objResult;

    }




    public function query($query){


      echo "################ ". substr($query, 0, 100)." ################<br><br>";

      $objAllDatabase = $this->readAllDatabase();
      foreach($objAllDatabase->dbname as $dbname){
        $select = mysql_select_db($dbname, $this->conn);
        if (!$select) {
            die ('Table unavailable : ' . mysql_error());
        }else{
          $row = mysql_query($query, $this->conn);
          if($row){
            echo "Query is affected ".$row." rows on <b>".$dbname."</b><br>";
          }else{
            echo "Query is affected 0 rows on <b>".$dbname."</b><br>";
          }
        }
      }

      echo "################ End upgrade state. ################<br><br>";

    }

  }


  //Main function
  $dbhost = "localhost";
  $dbuser = "root";
  $dbpass = "root";
  $db = new SQLQuery($dbhost, $dbuser, $dbpass);


  //###########################################
  // Desc : ADD COLUMN `tcp_subcategory_id` add `zk_icarrent_topics`
  // Create : 2012-04-19
  // Update By : Foppy
  //###########################################
  $query = "ALTER TABLE `zk_icarrent_topics`
            ADD COLUMN `tcp_subcategory_id` INT(11) NULL AFTER `tpc_category_id`;"
  $db->query($query);


  //###########################################
  // Desc : ALTER TABLE zk_icarrent_shop
  // Create : 2012-04-19
  // Update By : Foppy
  //###########################################
  $query = "ALTER TABLE `zk_icarrent_shop`
            ADD COLUMN `shp_district_id` INT(11) NOT NULL AFTER `shp_address2`,
            CHANGE `shp_province` `shp_province_id` INT(11) NOT NULL;"
  $db->query($query);


  //###########################################
  // Desc : ALTER TABLE `zk_icarrent_pickup_location`
  // Create : 2012-04-23
  // Update By : Foppy
  //###########################################
  $query = "DROP TABLE `zk_icarrent_pickup_location`;
            CREATE TABLE `zk_icarrent_pickup_location` (
              `pik_id` INT(11) NOT NULL AUTO_INCREMENT,
              `pik_name` VARCHAR(255) NOT NULL,
              `pik_province_id` INT(11) NOT NULL,
              `pik_district_id` INT(11) NOT NULL,
              `pik_shop_id` INT(11) NOT NULL,
              `pik_latitude` VARCHAR(255) NOT NULL,
              `pik_longitude` VARCHAR(255) NOT NULL,
              `pik_obj_status` VARCHAR(1) NOT NULL DEFAULT 'A',
              `pik_cr_date` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
              `pik_cr_uid` INT(11) NOT NULL DEFAULT '0',
              `pik_lu_date` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
              `pik_lu_uid` INT(11) NOT NULL DEFAULT '0',
              PRIMARY KEY  (`pik_id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8;"
  $db->query($query);

  //###########################################
  // Desc : ALTER TABLE `zk_icarrent_service_area`
  // Create : 2012-04-23
  // Update By : Foppy
  //###########################################
  $query = "DROP TABLE `zk_icarrent_service_area`;

            CREATE TABLE `zk_icarrent_service_area` (
              `ser_id` INT(11) NOT NULL AUTO_INCREMENT,
              `ser_shop_id` VARCHAR(255) NOT NULL,
              `ser_province_id` INT(11) NOT NULL,
              `ser_district_id` INT(11) NOT NULL,
              `ser_obj_status` VARCHAR(1) NOT NULL DEFAULT 'A',
              `ser_cr_date` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
              `ser_cr_uid` INT(11) NOT NULL DEFAULT '0',
              `ser_lu_date` DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',
              `ser_lu_uid` INT(11) NOT NULL DEFAULT '0',
              PRIMARY KEY  (`ser_id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8;"
  $db->query($query);




  //Add field
  $query = "ALTER TABLE `zk_icarrent_shop`
              ADD COLUMN `shp_policy` TEXT NULL AFTER `shp_dropoff_location`;"
  $db->query($query);

  //Delete Field
  $query = "ALTER TABLE `zk_icarrent_shop`
             DROP `tcp_subcategory_id`;"
  $db->query($query);


//shp_pickup_location



  //###########################################
  // Desc : CREATE TABLE `zk_icarrent_reserve`
  // Create : 2012-05-29
  // Update By : Foppy
  //###########################################
  $query = "DROP TABLE `zk_icarrent_reserve`;

            CREATE TABLE `zk_icarrent_reserve` (
              `rsv_id` int(11) NOT NULL auto_increment,
              `rsv_user_id` int(11) default NULL,
              `rsv_reserver_firstname` varchar(255) default NULL,
              `rsv_reserver_lastname` varchar(255) default NULL,
              `rsv_reserver_phone` varchar(20) default NULL,
              `rsv_reserver_email` varchar(255) default NULL,
              `rsv_reserver_ip_address` varchar(255) default NULL,
              `rsv_topics_id` int(11) NOT NULL,
              `rsv_start_date` datetime NOT NULL,
              `rsv_start_location` int(11) NOT NULL,
              `rsv_end_date` datetime NOT NULL,
              `rsv_end_location` int(11) NOT NULL,
              `rsv_total_charge` int(11) NOT NULL,
              `rsv_obj_status` varchar(1) NOT NULL default 'A',
              `rsv_cr_date` datetime NOT NULL default '1970-01-01 00:00:00',
              `rsv_cr_uid` int(11) NOT NULL default '0',
              `rsv_lu_date` datetime NOT NULL default '1970-01-01 00:00:00',
              `rsv_lu_uid` int(11) NOT NULL default '0',
              PRIMARY KEY  (`rsv_id`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8;"
    $db->query($query);


    $query = "ALTER TABLE `zk_icarrent_reserve`
              ADD COLUMN `rsv_reserv_status` VARCHAR(20) NULL AFTER `rsv_total_charge`;
              ALTER TABLE `zk_icarrent_reserve`
              ADD COLUMN `rsv_confirmation_code` VARCHAR(20) NULL AFTER `rsv_reserv_status`;"
    $db->query($query);



?>